﻿using Google.ProtocolBuffers;
using LightingCache;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Linq;
using System.Text;

namespace LightingServer
{
    public class BusinessService
    {
        protected bool TryGetValue(object value, out string result)
        {
            result = null;
            if (!DBNull.Value.Equals(value))
            {
                result = value.ToString();
                if (!string.IsNullOrEmpty(result))
                {
                    return true;
                }
            }
            return false;
        }

        protected bool TryGetValue(object value, out uint result)
        {
            result = 0;
            if (!DBNull.Value.Equals(value))
            {
                return uint.TryParse(value.ToString(), out result);
            }
            return false;
        }

        protected bool TryGetValue(object value, out double result)
        {
            result = 0;
            if (!DBNull.Value.Equals(value))
            {
                return double.TryParse(value.ToString(), out result);
            }
            return false;
        }

        protected bool TryGetValue(object value, out byte[] result)
        {
            result = null;
            if (!DBNull.Value.Equals(value))
            {
                try
                {
                    byte[] array = (byte[])value;
                    if (array != null && array.Length > 0)
                    {
                        result = array;
                        return true;
                    }
                }
                catch
                {
                }
            }
            return false;
        }
    }

    public class UserService : BusinessService
    {
        public Users GetUsers()
        {
            CacheManager cacheManager = new CacheManager();
            byte[] bytes = cacheManager.Get<byte[]>(LightingServerContext.UserKey);
            if (bytes != null)
                return Users.ParseFrom(bytes);

            Users.Builder builder = new Users.Builder();
            try
            {
                using (DbConnection conn = LightingServerContext.CreateConnection())
                {
                    using (DbCommand command = conn.CreateCommand())
                    {
                        conn.Open();

                        command.CommandText = "SELECT id, name, password, roleType FROM user;";
                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                builder.UserList.Add(CreateUser(reader));
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
            }

            Users users = builder.Build();
            cacheManager.Add(LightingServerContext.UserKey, users.ToByteArray(), DateTime.Now.AddDays(1));
            return users;
        }

        public User CreateUser(DbDataReader reader)
        {
            User.Builder userBuilder = new User.Builder();
            userBuilder.SetId(reader["id"].ToString());
            userBuilder.SetName(reader["name"].ToString());
            userBuilder.SetPassword(reader["password"].ToString());
            userBuilder.SetRoleType(uint.Parse(reader["roleType"].ToString()));
            return userBuilder.Build();
        }

        public bool AddUser(User user)
        {
            bool isSuccessful = false;
            try
            {
                using (DbConnection conn = LightingServerContext.CreateConnection())
                {
                    using (DbCommand command = conn.CreateCommand())
                    {
                        conn.Open();

                        command.CommandText = "INSERT INTO user(id,name,password,roleType) values(@id,@name,@password,@roleType);";
                        command.Parameters.AddRange(new MySqlParameter[]
                        {
                            new MySqlParameter()
                            {
                                ParameterName = "@id",
                                DbType = DbType.String,
                                Value = user.Id
                            },
                            new MySqlParameter()
                            {
                                ParameterName = "@name",
                                DbType = DbType.String,
                                Value = user.Name
                            },
                            new MySqlParameter()
                            {
                                ParameterName = "@password",
                                DbType = DbType.String,
                                Value = user.Name
                            },
                            new MySqlParameter()
                            {
                                ParameterName = "@roleType",
                                DbType = DbType.UInt32,
                                Value = user.RoleType
                            }
                        });

                        isSuccessful = command.ExecuteNonQuery() > 0;

                        if (isSuccessful)
                        {
                            CacheManager cacheManager = new CacheManager(LightingServerContext.CacheUrl);
                            cacheManager.Initialize();
                            if (cacheManager.Contains(LightingServerContext.UserKey))
                            {
                                byte[] bytes = (byte[])cacheManager[LightingServerContext.UserKey];
                                if (bytes != null)
                                {
                                    Users users = Users.ParseFrom(bytes);
                                    Users.Builder builder = users.ToBuilder();
                                    builder.UserList.Add(user);
                                }
                            }
                            cacheManager.ShutDown();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return isSuccessful;
        }

        public bool UpdateUser(User user)
        {
            StringBuilder sb = new StringBuilder();
            List<MySqlParameter> list = new List<MySqlParameter>();
            if (user.HasName)
            {
                sb.Append("name=@name,");
                list.Add(new MySqlParameter()
                {
                    ParameterName = "@name",
                    DbType = DbType.String,
                    Value = user.Name
                });
            }

            if (user.HasPassword)
            {
                sb.Append("password=@password,");
                list.Add(new MySqlParameter()
                {
                    ParameterName = "@password",
                    DbType = DbType.String,
                    Value = user.Password
                });
            }

            if (user.HasRoleType)
            {
                sb.Append("roleType=@roleType,");
                list.Add(new MySqlParameter()
                {
                    ParameterName = "@roleType",
                    DbType = DbType.UInt32,
                    Value = user.RoleType
                });
            }

            if (sb.Length == 0)
            {
                return false;
            }

            sb.Remove(sb.Length - 1, 1);

            sb.Insert(0, "UPDATE user SET ");
            sb.Append(" WHERE id=@id;");
            list.Add(new MySqlParameter()
            {
                ParameterName = "@id",
                DbType = DbType.String,
                Value = user.Id
            });

            bool isSuccessful = false;
            try
            {
                using (DbConnection conn = LightingServerContext.CreateConnection())
                {
                    using (DbCommand command = conn.CreateCommand())
                    {
                        conn.Open();

                        command.CommandText = sb.ToString();
                        command.Parameters.AddRange(list.ToArray());
                        isSuccessful = command.ExecuteNonQuery() > 0;

                        if (isSuccessful)
                            LightingServerContext.UserCache[user.Id] = user;

                    }
                }
            }
            catch (Exception ex)
            {
            }

            return isSuccessful;
        }

        public bool DeleteUser(string id)
        {

            bool isSuccessful = false;
            try
            {
                using (DbConnection conn = LightingServerContext.CreateConnection())
                {
                    using (DbCommand command = conn.CreateCommand())
                    {
                        conn.Open();

                        command.CommandText = "DELETE FROM user WHERE id=@id;";
                        command.Parameters.Add(new MySqlParameter()
                        {
                            ParameterName = "@id",
                            DbType = DbType.String,
                            Value = id
                        });

                        isSuccessful = command.ExecuteNonQuery() > 0;

                        if (isSuccessful && LightingServerContext.UserCache.ContainsKey(id))
                            LightingServerContext.UserCache.Remove(id);
                    }
                }
            }
            catch (Exception ex)
            {
            }

            return isSuccessful;
        }
    }

    public class LightingService : BusinessService
    {
        public bool AddLighting(Lighting lighting)
        {
            bool result = false;

            try
            {
                using (MySqlConnection conn = (MySqlConnection)LightingServerContext.CreateConnection())
                {
                    conn.Open();

                    MySqlTransaction tran = null;
                    try
                    {
                        tran = conn.BeginTransaction();
                        LightingCreator creator = new LightingCreator();
                        creator.Create(conn, lighting);
                        tran.Commit();
                        result = true;
                    }
                    catch (Exception ex)
                    {
                        if (tran != null)
                            tran.Rollback();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return result;
        }

        public bool EditLighting(Lighting lighting)
        {
            if (DeleteLighting(lighting))
                return AddLighting(lighting);
            return false;
        }

        public bool DeleteLighting(Lighting lighting)
        {
            bool result = false;

            try
            {
                using (MySqlConnection conn = (MySqlConnection)LightingServerContext.CreateConnection())
                {
                    conn.Open();

                    MySqlTransaction tran = null;
                    try
                    {
                        tran = conn.BeginTransaction();
                        LightingDelete del = new LightingDelete();
                        del.Delete(conn, lighting);
                        tran.Commit();
                        result = true;
                    }
                    catch (Exception ex)
                    {
                        if (tran != null)
                            tran.Rollback();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return result;
        }
    }


    public partial class QueryService : BusinessService
    {
        private static readonly Dictionary<string, string> _angels;
        static QueryService()
        {
            _angels = new Dictionary<string, string>();
            _angels["SS"] = "1";
            _angels["S"] = "2";
            _angels["M"] = "3";
            _angels["F"] = "4";
            _angels["WF"] = "5";
            _angels["WW"] = "6";
            _angels["ST"] = "7";

        }

        //SELECT DISTINCT A.class productClass, A.lable lable, A.series series, E.class adpaterClass, H.facade facade
        //FROM lighting A,adapterlighting E, project F, picture H
        //WHERE A.projectID = F.id
        //AND A.adapterID = E.id
        //AND A.pictureID = H.id
        //AND A.class = 2
        //AND A.place = 1
        //AND F.setup = 2
        public Lightings QueryLevel2(QueryParameter2 parameter)
        {
            Lightings.Builder resultBuilder = new Lightings.Builder();
            try
            {
                using (DbConnection conn = LightingServerContext.CreateConnection())
                {
                    using (DbCommand command = conn.CreateCommand())
                    {
                        conn.Open();

                        StringBuilder sb = new StringBuilder();
                        sb.Append("SELECT DISTINCT A.class productClass, A.lable lable, A.series series, E.class adpaterClass, H.facade facade ");
                        sb.Append("FROM lighting A,adapterlighting E, project F, picture H ");
                        sb.Append("WHERE A.projectID = F.id AND A.adapterID = E.id AND A.pictureID = H.id ");
                        if (parameter.HasClass)
                        {
                            sb.Append("AND A.class = @class ");
                            command.Parameters.Add(new MySqlParameter()
                            {
                                ParameterName = "@class",
                                DbType = DbType.UInt32,
                                Value = parameter.Class
                            });
                        }

                        if (parameter.HasUsePlace)
                        {
                            sb.Append("AND A.place = @place ");
                            command.Parameters.Add(new MySqlParameter()
                            {
                                ParameterName = "@place",
                                DbType = DbType.UInt32,
                                Value = parameter.UsePlace
                            });
                        }

                        if (parameter.HasSetup)
                        {
                            sb.Append("AND F.setup = @setup ");
                            command.Parameters.Add(new MySqlParameter()
                            {
                                ParameterName = "@setup",
                                DbType = DbType.UInt32,
                                Value = parameter.Setup
                            });
                        }
                        sb.Append(";");

                        command.CommandText = sb.ToString();

                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Lighting.Builder itemBuilder = new Lighting.Builder();

                                object productClass = reader["productClass"];
                                if (!DBNull.Value.Equals(productClass))
                                {
                                    uint value;
                                    if (uint.TryParse(productClass.ToString(), out value))
                                        itemBuilder.SetClass(value);
                                }

                                object lable = reader["lable"];
                                if (!DBNull.Value.Equals(lable))
                                {
                                    string value = lable.ToString();
                                    if (!string.IsNullOrEmpty(value))
                                        itemBuilder.SetLable(value);
                                }

                                object series = reader["series"];
                                if (!DBNull.Value.Equals(series))
                                {
                                    string value = series.ToString();
                                    if (!string.IsNullOrEmpty(value))
                                        itemBuilder.SetSeries(value);
                                }

                                object adpaterClass = reader["adpaterClass"];
                                if (!DBNull.Value.Equals(adpaterClass))
                                {
                                    uint value;
                                    if (uint.TryParse(adpaterClass.ToString(), out value))
                                    {
                                        AdapterLighting.Builder adapterBuilder = new AdapterLighting.Builder();
                                        adapterBuilder.SetClass(value);
                                        itemBuilder.SetAdapterLighting(adapterBuilder.Build());
                                    }
                                }

                                object facade = reader["facade"];
                                if (!DBNull.Value.Equals(facade))
                                {
                                    try
                                    {
                                        string value = facade.ToString();
                                        if (!string.IsNullOrEmpty(value))
                                        {
                                            Picture.Builder pictureBuilder = new Picture.Builder();
                                            string imageUrl = Path.Combine(LightingServerContext.ImageUrl, value);
                                            pictureBuilder.SetFacade(imageUrl);
                                            itemBuilder.SetPicture(pictureBuilder.Build());
                                        }
                                    }
                                    catch
                                    {
                                    }
                                }

                                resultBuilder.LightingList.Add(itemBuilder.Build());
                            }

                        }
                    }
                }
            }
            catch (Exception ex)
            {

            }
            return resultBuilder.Build();
        }

        /*
        SELECT A.id, H.facade facade, H.facadeExt facadeExt, A.series series, A.class productClass, A.version version, A.lable lable, B.size size,
               E.class adpaterClass, D.lightingPower power, E.color color,
               E.cri cri, C.angle angle,
               G.market market
        FROM lighting A,physical B,optics C, electric D, adapterlighting E, project F, business G, picture H
        WHERE A.physicalID = B.id
        AND A.opticsID = C.id
        AND A.electricid = D.id
        AND A.adapterID = E.id
        AND A.projectID = F.id
        AND A.businessID = G.id
        AND A.pictureID = H.id
        AND A.class = 2
        AND A.place = 1
        AND A.lable = 1
        AND A.series = 1
        AND E.class = 1
        AND F.setup = 1
        */
        public Lightings QueryLevel3(QueryParameter3 parameter)
        {
            Lightings.Builder resultBuilder = new Lightings.Builder();
            try
            {
                using (DbConnection conn = LightingServerContext.CreateConnection())
                {
                    StringBuilder detailPrefixBuilder = new StringBuilder();
                    detailPrefixBuilder.Append("SELECT A.id id, A.place place, H.facade facade,  A.series series, A.class productClass, A.version version, A.lable lable, B.size size,");
                    detailPrefixBuilder.Append("E.class adpaterClass, D.lightingPower power, E.color color,E.cri cri, C.angle angle,G.market market ");

                    StringBuilder middleBuilder = new StringBuilder();
                    middleBuilder.Append("FROM lighting A,physical B,optics C, electric D, adapterlighting E, project F, business G, picture H ");
                    middleBuilder.Append("WHERE A.physicalID = B.id ");
                    middleBuilder.Append("AND A.opticsID = C.id ");
                    middleBuilder.Append("AND A.electricid = D.id ");
                    middleBuilder.Append("AND A.adapterID = E.id ");
                    middleBuilder.Append("AND A.projectID = F.id ");
                    middleBuilder.Append("AND A.businessID = G.id ");
                    middleBuilder.Append("AND A.pictureID = H.id ");

                    List<MySqlParameter> parameters = new List<MySqlParameter>();
                    if (parameter.HasProductClass)
                    {
                        middleBuilder.Append("AND A.class = @productClass ");
                        parameters.Add(new MySqlParameter()
                        {
                            ParameterName = "@productClass",
                            DbType = DbType.UInt32,
                            Value = parameter.ProductClass
                        });
                    }

                    if (parameter.HasUsePlace)
                    {
                        middleBuilder.Append("AND A.place = @place ");
                        parameters.Add(new MySqlParameter()
                        {
                            ParameterName = "@place",
                            DbType = DbType.UInt32,
                            Value = parameter.UsePlace
                        });
                    }

                    if (parameter.HasLable)
                    {
                        middleBuilder.Append("AND A.lable = @lable ");
                        parameters.Add(new MySqlParameter()
                        {
                            ParameterName = "@lable",
                            DbType = DbType.String,
                            Value = parameter.Lable
                        });
                    }


                    if (parameter.HasSeries)
                    {
                        middleBuilder.Append("AND A.series = @series ");
                        parameters.Add(new MySqlParameter()
                        {
                            ParameterName = "@series",
                            DbType = DbType.String,
                            Value = parameter.Series
                        });
                    }

                    if (parameter.HasAdpaterClass)
                    {
                        middleBuilder.Append("AND E.class = @adpaterClass ");
                        parameters.Add(new MySqlParameter()
                        {
                            ParameterName = "@adpaterClass",
                            DbType = DbType.UInt32,
                            Value = parameter.AdpaterClass
                        });
                    }

                    if (parameter.HasSetup)
                    {
                        middleBuilder.Append("AND F.setup = @setup ");
                        parameters.Add(new MySqlParameter()
                        {
                            ParameterName = "@setup",
                            DbType = DbType.UInt32,
                            Value = parameter.Setup
                        });
                    }

                    conn.Open();

                    using (DbCommand command = conn.CreateCommand())
                    {
                        StringBuilder sb = new StringBuilder();
                        sb.Append(detailPrefixBuilder.ToString());
                        sb.Append(middleBuilder.ToString() + ";");
                        command.CommandText = sb.ToString();
                        command.Parameters.AddRange(parameters.ToArray());

                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                                resultBuilder.LightingList.Add(CreateLighting3(reader));
                        }
                    }

                }
            }
            catch (Exception ex)
            {

            }
            return resultBuilder.Build();
        }

        private Lighting CreateLighting3(DbDataReader reader)
        {
            Lighting.Builder itemBuilder = new Lighting.Builder();
            itemBuilder.SetId(reader["id"].ToString());

            string stringValue;
            if (TryGetValue(reader["facade"], out stringValue))
            {
                Picture.Builder builder = new Picture.Builder();
                string imageUrl = Path.Combine(LightingServerContext.ImageUrl, stringValue);
                builder.SetFacade(imageUrl);
                itemBuilder.SetPicture(builder.Build());
            }

            if (TryGetValue(reader["place"], out stringValue))
                itemBuilder.SetSeries(stringValue);

            if (TryGetValue(reader["series"], out stringValue))
                itemBuilder.SetSeries(stringValue);

            uint uintValue;
            if (TryGetValue(reader["productClass"], out uintValue))
                itemBuilder.SetClass(uintValue);

            if (TryGetValue(reader["version"], out stringValue))
                itemBuilder.SetVersion(stringValue);

            if (TryGetValue(reader["lable"], out stringValue))
                itemBuilder.SetLable(stringValue);

            if (TryGetValue(reader["size"], out stringValue))
            {
                Physical.Builder builder = new Physical.Builder();
                builder.SetSize(stringValue);
                itemBuilder.SetPhysical(builder.Build());
            }

            AdapterLighting.Builder adapterBuilder = null;
            if (TryGetValue(reader["adpaterClass"], out uintValue))
            {
                adapterBuilder = new AdapterLighting.Builder();
                adapterBuilder.SetClass(uintValue);
            }

            if (TryGetValue(reader["color"], out stringValue))
            {
                if (adapterBuilder == null)
                    adapterBuilder = new AdapterLighting.Builder();
                adapterBuilder.SetColor(stringValue);
            }

            if (TryGetValue(reader["cri"], out uintValue))
            {
                if (adapterBuilder == null)
                    adapterBuilder = new AdapterLighting.Builder();
                adapterBuilder.SetCri(uintValue);
            }

            if (adapterBuilder != null)
                itemBuilder.SetAdapterLighting(adapterBuilder.Build());

            if (TryGetValue(reader["power"], out uintValue))
            {
                Electric.Builder builder = new Electric.Builder();
                builder.SetLightingPower(uintValue);
                itemBuilder.SetElectric(builder.Build());
            }

            if (TryGetValue(reader["angle"], out stringValue))
            {
                Optics.Builder builder = new Optics.Builder();
                builder.SetAngle(stringValue);
                itemBuilder.SetOptics(builder.Build());
            }

            double doubleValue;
            if (TryGetValue(reader["market"], out doubleValue))
            {
                Business.Builder builder = new Business.Builder();
                builder.SetMarket(doubleValue);
                itemBuilder.SetBusiness(builder.Build());
            }

            return itemBuilder.Build();
        }

        /*
        SELECT A.id,A.series series,A.class productClass,A.place place,A.version version,A.lable lable,
               B.color physicalColor,B.size size,B.defend defend,B.impact impact,E.class adpaterClass,
               D.lightingPower power,E.color adpaterColor,E.cri cri,C.angle angle,G.market market,
               H.facade facade,H.facadeExt facadeExt,H.live live,H.liveExt liveExt,H.size pictureSize,H.sizeExt pictureSizeExt,H.grading1 grading1, H.grading1Ext grading1Ext
        FROM lighting A,physical B,optics C, electric D, adapterlighting E, project F, business G, picture H
        WHERE A.physicalID = B.id
        AND A.opticsID = C.id
        AND A.electricid = D.id
        AND A.adapterID = E.id
        AND A.projectID = F.id
        AND A.businessID = G.id
        AND A.pictureID = H.id
        AND A.id=1
         */
        public Lighting QueryLevel4(string id)
        {
            Lighting lighting = null;
            try
            {
                using (DbConnection conn = LightingServerContext.CreateConnection())
                {
                    using (DbCommand command = conn.CreateCommand())
                    {
                        conn.Open();

                        StringBuilder sb = new StringBuilder();
                        sb.Append("SELECT A.id id,A.series series,A.class productClass,A.place place,A.version version,A.lable lable,");
                        sb.Append("B.color physicalColor,B.size size,B.defend defend,B.impact impact,E.class adpaterClass,");
                        sb.Append("D.lightingPower power,E.color adpaterColor,E.cri cri,C.angle angle,G.market market,");
                        sb.Append("H.facade facade,H.live live,H.size pictureSize,H.grading1 grading1 ");
                        sb.Append("FROM lighting A,physical B,optics C, electric D, adapterlighting E, project F, business G, picture H ");
                        sb.Append("WHERE A.physicalID = B.id ");
                        sb.Append("AND A.opticsID = C.id ");
                        sb.Append("AND A.electricid = D.id ");
                        sb.Append("AND A.adapterID = E.id ");
                        sb.Append("AND A.projectID = F.id ");
                        sb.Append("AND A.businessID = G.id ");
                        sb.Append("AND A.pictureID = H.id ");
                        sb.Append("AND A.id= @id;");
                        command.CommandText = sb.ToString();
                        command.Parameters.Add(new MySqlParameter()
                        {
                            ParameterName = "@id",
                            DbType = DbType.String,
                            Value = id
                        });

                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                lighting = CreateLighting4(reader);
                                break;
                            }

                        }
                    }
                }
            }
            catch (Exception ex)
            {

            }
            return lighting != null ? lighting : new Lighting.Builder().Build();
        }

        private Lighting CreateLighting4(DbDataReader reader)
        {
            Lighting.Builder lightingBuilder = new Lighting.Builder();
            lightingBuilder.SetId(reader["id"].ToString());

            string stringValue;
            if (TryGetValue(reader["series"], out stringValue))
                lightingBuilder.SetSeries(stringValue);

            uint uintValue;
            if (TryGetValue(reader["productClass"], out uintValue))
                lightingBuilder.SetClass(uintValue);

            if (TryGetValue(reader["version"], out stringValue))
                lightingBuilder.SetVersion(stringValue);

            if (TryGetValue(reader["lable"], out stringValue))
                lightingBuilder.SetLable(stringValue);

            if (TryGetValue(reader["place"], out uintValue))
                lightingBuilder.SetPlace(uintValue);

            Physical.Builder physicalBuilder = null;

            if (TryGetValue(reader["physicalColor"], out stringValue))
            {
                physicalBuilder = new Physical.Builder();
                physicalBuilder.SetColor(stringValue);
            }

            if (TryGetValue(reader["size"], out stringValue))
            {
                if (physicalBuilder == null)
                    physicalBuilder = new Physical.Builder();
                physicalBuilder.SetSize(stringValue);
            }

            if (TryGetValue(reader["defend"], out stringValue))
            {
                if (physicalBuilder == null)
                    physicalBuilder = new Physical.Builder();
                physicalBuilder.SetDefend(stringValue);
            }

            if (TryGetValue(reader["impact"], out uintValue))
            {
                if (physicalBuilder == null)
                    physicalBuilder = new Physical.Builder();
                physicalBuilder.SetImpact(uintValue);
            }

            if (physicalBuilder != null)
                lightingBuilder.SetPhysical(physicalBuilder.Build());

            AdapterLighting.Builder adapterBuilder = null;
            if (TryGetValue(reader["adpaterClass"], out uintValue))
            {
                adapterBuilder = new AdapterLighting.Builder();
                adapterBuilder.SetClass(uintValue);
            }

            if (TryGetValue(reader["adpaterColor"], out stringValue))
            {
                if (adapterBuilder == null)
                    adapterBuilder = new AdapterLighting.Builder();
                adapterBuilder.SetColor(stringValue);
            }

            if (TryGetValue(reader["cri"], out uintValue))
            {
                if (adapterBuilder == null)
                    adapterBuilder = new AdapterLighting.Builder();
                adapterBuilder.SetCri(uintValue);
            }

            if (adapterBuilder != null)
                lightingBuilder.SetAdapterLighting(adapterBuilder.Build());

            if (TryGetValue(reader["power"], out uintValue))
            {
                Electric.Builder builder = new Electric.Builder();
                builder.SetLightingPower(uintValue);
                lightingBuilder.SetElectric(builder.Build());
            }

            if (TryGetValue(reader["angle"], out stringValue))
            {
                Optics.Builder builder = new Optics.Builder();
                builder.SetAngle(stringValue);
                lightingBuilder.SetOptics(builder.Build());
            }

            double doubleValue;
            if (TryGetValue(reader["market"], out doubleValue))
            {
                Business.Builder builder = new Business.Builder();
                builder.SetMarket(doubleValue);
                lightingBuilder.SetBusiness(builder.Build());
            }

            Picture.Builder pictureBuilder = null;
            if (TryGetValue(reader["facade"], out stringValue))
            {
                pictureBuilder = new Picture.Builder();
                string imageUrl = Path.Combine(LightingServerContext.ImageUrl, stringValue);
                pictureBuilder.SetFacade(imageUrl);
            }

            if (TryGetValue(reader["live"], out stringValue))
            {
                if (pictureBuilder == null)
                    pictureBuilder = new Picture.Builder();
                string imageUrl = Path.Combine(LightingServerContext.ImageUrl, stringValue);
                pictureBuilder.SetLive(imageUrl);
            }

            if (TryGetValue(reader["pictureSize"], out stringValue))
            {
                if (pictureBuilder == null)
                    pictureBuilder = new Picture.Builder();
                string imageUrl = Path.Combine(LightingServerContext.ImageUrl, stringValue);
                pictureBuilder.SetSize(imageUrl);
            }

            if (TryGetValue(reader["grading1"], out stringValue))
            {
                if (pictureBuilder == null)
                    pictureBuilder = new Picture.Builder();
                string imageUrl = Path.Combine(LightingServerContext.ImageUrl, stringValue);
                pictureBuilder.SetGrading1(imageUrl);
            }

            if (pictureBuilder != null)
                lightingBuilder.SetPicture(pictureBuilder.Build());

            return lightingBuilder.Build();
        }

        /*
        SELECT A.id,A.series,A.class productClass,A.place,A.version,A.lable,A.orgin,
               A.physicalID,A.opticsID,A.electricID,A.adapterID,A.projectID,A.businessID,A.pictureID,
               B.material,B.color physicalColor,B.size physicalsize,B.defend,B.impact,B.weight,
               C.explain1,C.angle,C.efficiency,
               D.shock,D.voltage,D.type electrictType,D.electricPower,D.lightingPower,D.allPower,D.controlType,
               E.class adpaterClass,E.version adpaterVersion,E.color adpaterColor,E.cri,E.output,E.pins,
               F.parts,F.setup,F.size projectSize,F.report,F.baikup,
               G.in, G.market,G.out,G.delievery, H.facade,H.size pictureSize,H.grading1,H.live
        FROM lighting A,physical B,optics C, electric D, adapterlighting E, project F, business G, picture H
        WHERE A.physicalID = B.id
        AND A.opticsID = C.id
        AND A.electricid = D.id
        AND A.adapterID = E.id
        AND A.projectID = F.id
        AND A.businessID = G.id
        AND A.pictureID = H.id
        AND A.id=1
         */
        public Lighting QueryLevel5(string id)
        {
            Lighting lighting = null;
            try
            {
                using (DbConnection conn = LightingServerContext.CreateConnection())
                {
                    using (DbCommand command = conn.CreateCommand())
                    {
                        conn.Open();

                        StringBuilder sb = new StringBuilder();
                        sb.Append("SELECT A.id,A.series,A.class productClass,A.place,A.version,A.lable,A.orgin,");
                        sb.Append("A.physicalID,A.opticsID,A.electricID,A.adapterID,A.projectID,A.businessID,A.pictureID,");
                        sb.Append("B.material,B.color physicalColor,B.size physicalsize,B.defend,B.impact,B.weight,");
                        sb.Append("C.explain1,C.angle,C.efficiency,");
                        sb.Append("D.shock,D.voltage,D.type electrictType,D.electricPower,D.lightingPower,D.allPower,D.controlType,");
                        sb.Append("E.class adpaterClass,E.version adpaterVersion,E.color adpaterColor,E.cri,E.output,E.pins,");
                        sb.Append("F.parts,F.setup,F.size projectSize,F.report,F.baikup,");
                        sb.Append("G.in, G.market,G.out,G.delievery, H.facade facade,H.live live,H.size pictureSize,H.grading1 grading1 ");
                        sb.Append("FROM lighting A,physical B,optics C, electric D, adapterlighting E, project F, business G, picture H ");
                        sb.Append("WHERE A.physicalID = B.id ");
                        sb.Append("AND A.opticsID = C.id ");
                        sb.Append("AND A.electricid = D.id ");
                        sb.Append("AND A.adapterID = E.id ");
                        sb.Append("AND A.projectID = F.id ");
                        sb.Append("AND A.businessID = G.id ");
                        sb.Append("AND A.pictureID = H.id ");
                        sb.Append("AND A.id= @id;");

                        command.CommandText = sb.ToString();
                        command.Parameters.Add(new MySqlParameter()
                        {
                            ParameterName = "@id",
                            DbType = DbType.String,
                            Value = id
                        });

                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                lighting = CreateLighting5(reader);
                                break;
                            }

                        }
                    }
                }
            }
            catch (Exception ex)
            {

            }
            return lighting != null ? lighting : new Lighting.Builder().Build();
        }

        private Lighting CreateLighting5(DbDataReader reader)
        {
            Lighting.Builder lightingBuilder = new Lighting.Builder();
            lightingBuilder.SetId(reader["id"].ToString());

            string stringValue;
            if (TryGetValue(reader["series"], out stringValue))
                lightingBuilder.SetSeries(stringValue);

            uint uintValue;
            if (TryGetValue(reader["productClass"], out uintValue))
                lightingBuilder.SetClass(uintValue);
            if (TryGetValue(reader["version"], out stringValue))
                lightingBuilder.SetVersion(stringValue);
            if (TryGetValue(reader["lable"], out stringValue))
                lightingBuilder.SetLable(stringValue);
            if (TryGetValue(reader["place"], out uintValue))
                lightingBuilder.SetPlace(uintValue);
            if (TryGetValue(reader["orgin"], out uintValue))
                lightingBuilder.SetOrgin(uintValue);

            Physical.Builder physicalBuilder = new Physical.Builder();
            physicalBuilder.SetId(reader["physicalID"].ToString());
            if (TryGetValue(reader["material"], out stringValue))
                physicalBuilder.SetMaterial(stringValue);
            if (TryGetValue(reader["physicalColor"], out stringValue))
                physicalBuilder.SetColor(stringValue);
            if (TryGetValue(reader["physicalsize"], out stringValue))
                physicalBuilder.SetSize(stringValue);
            if (TryGetValue(reader["defend"], out stringValue))
                physicalBuilder.SetDefend(stringValue);
            if (TryGetValue(reader["impact"], out uintValue))
                physicalBuilder.SetImpact(uintValue);
            double doubleValue;
            if (TryGetValue(reader["weight"], out doubleValue))
                physicalBuilder.SetWeight(uintValue);
            lightingBuilder.SetPhysical(physicalBuilder.Build());

            Optics.Builder opticsBuilder = new Optics.Builder();
            opticsBuilder.SetId(reader["opticsID"].ToString());
            if (TryGetValue(reader["explain1"], out uintValue))
                opticsBuilder.SetExplain(uintValue);
            if (TryGetValue(reader["angle"], out stringValue))
                opticsBuilder.SetAngle(stringValue);
            if (TryGetValue(reader["efficiency"], out doubleValue))
                opticsBuilder.SetEfficiency(doubleValue);
            lightingBuilder.SetOptics(opticsBuilder.Build());

            Electric.Builder electricBuilder = new Electric.Builder();
            electricBuilder.SetId(reader["electricID"].ToString());
            if (TryGetValue(reader["shock"], out stringValue))
                electricBuilder.SetShock(stringValue);
            if (TryGetValue(reader["voltage"], out uintValue))
                electricBuilder.SetVoltage(uintValue);
            if (TryGetValue(reader["electrictType"], out stringValue))
                electricBuilder.SetType(stringValue);
            if (TryGetValue(reader["electricPower"], out uintValue))
                electricBuilder.SetElectricPower(uintValue);
            if (TryGetValue(reader["lightingPower"], out uintValue))
                electricBuilder.SetLightingPower(uintValue);
            if (TryGetValue(reader["allPower"], out uintValue))
                electricBuilder.SetAllPower(uintValue);
            if (TryGetValue(reader["controlType"], out uintValue))
                electricBuilder.SetControlType(uintValue);
            lightingBuilder.SetElectric(electricBuilder.Build());

            AdapterLighting.Builder adapterBuilder = new AdapterLighting.Builder();
            adapterBuilder.SetId(reader["adapterID"].ToString());
            if (TryGetValue(reader["adpaterClass"], out uintValue))
                adapterBuilder.SetClass(uintValue);
            if (TryGetValue(reader["adpaterVersion"], out stringValue))
                adapterBuilder.SetVersion(stringValue);
            if (TryGetValue(reader["adpaterColor"], out stringValue))
                adapterBuilder.SetColor(stringValue);
            if (TryGetValue(reader["cri"], out uintValue))
                adapterBuilder.SetCri(uintValue);
            if (TryGetValue(reader["output"], out uintValue))
                adapterBuilder.SetOutput(uintValue);
            if (TryGetValue(reader["pins"], out stringValue))
                adapterBuilder.SetPins(stringValue);
            lightingBuilder.SetAdapterLighting(adapterBuilder.Build());

            Project.Builder projectBuilder = new Project.Builder();
            projectBuilder.SetId(reader["projectID"].ToString());
            if (TryGetValue(reader["parts"], out stringValue))
                projectBuilder.SetParts(stringValue);
            if (TryGetValue(reader["setup"], out uintValue))
                projectBuilder.SetSetup(uintValue);
            if (TryGetValue(reader["projectSize"], out stringValue))
                projectBuilder.SetSize(stringValue);
            if (TryGetValue(reader["report"], out stringValue))
                projectBuilder.SetReport(stringValue);
            if (TryGetValue(reader["baikup"], out stringValue))
                projectBuilder.SetBaikup(stringValue);
            lightingBuilder.SetProject(projectBuilder.Build());

            Business.Builder businessBuilder = new Business.Builder();
            projectBuilder.SetId(reader["businessID"].ToString());
            if (TryGetValue(reader["in"], out doubleValue))
                businessBuilder.SetIn(doubleValue);
            if (TryGetValue(reader["market"], out doubleValue))
                businessBuilder.SetMarket(doubleValue);
            if (TryGetValue(reader["out"], out doubleValue))
                businessBuilder.SetOut(doubleValue);
            if (TryGetValue(reader["delievery"], out stringValue))
                businessBuilder.SetDelievery(stringValue);
            lightingBuilder.SetBusiness(businessBuilder.Build());

            Picture.Builder pictureBuilder = new Picture.Builder();
            pictureBuilder.SetId(reader["pictureID"].ToString());
            if (TryGetValue(reader["facade"], out stringValue))
            {
                string imageUrl = Path.Combine(LightingServerContext.ImageUrl, stringValue);
                pictureBuilder.SetFacade(imageUrl);
            }

            if (TryGetValue(reader["pictureSize"], out stringValue))
            {
                string imageUrl = Path.Combine(LightingServerContext.ImageUrl, stringValue);
                pictureBuilder.SetSize(imageUrl);
            }

            if (TryGetValue(reader["grading1"], out stringValue))
            {
                string imageUrl = Path.Combine(LightingServerContext.ImageUrl, stringValue);
                pictureBuilder.SetGrading1(imageUrl);
            }

            if (TryGetValue(reader["live"], out stringValue))
            {
                string imageUrl = Path.Combine(LightingServerContext.ImageUrl, stringValue);
                pictureBuilder.SetLive(imageUrl);
            }
            lightingBuilder.SetPicture(pictureBuilder.Build());

            return lightingBuilder.Build();
        }

        private bool Filter(string targetAngel, string index)
        {
            if (string.IsNullOrEmpty(targetAngel))
                return index == "8";

            string key = targetAngel.ToUpper();
            if (_angels.ContainsKey(key))
                return _angels[key] == index;

            double angel;
            if (double.TryParse(targetAngel, out angel))
            {
                switch (index)
                {
                    case "1":
                        return angel < 8;
                    case "2":
                        return angel >= 9 && angel <= 15;
                    case "3":
                        return angel >= 16 && angel <= 25;
                    case "4":
                        return angel >= 26 && angel <= 39;
                    case "5":
                        return angel > 40;
                }
            }

            return false;
        }

        public Lightings Query(LightingQuery lightingQuery)
        {
            Lightings.Builder resultBuilder = new Lightings.Builder();
            try
            {
                using (DbConnection conn = LightingServerContext.CreateConnection())
                {
                    StringBuilder detailPrefixBuilder = new StringBuilder();
                    detailPrefixBuilder.Append("SELECT A.id,A.series,A.class productClass,A.place,A.version,A.lable,A.orgin,");
                    detailPrefixBuilder.Append("A.physicalID,A.opticsID,A.electricID,A.adapterID,A.projectID,A.businessID,A.pictureID,");
                    detailPrefixBuilder.Append("B.material,B.color physicalColor,B.size physicalsize,B.defend,B.impact,B.weight,");
                    detailPrefixBuilder.Append("C.explain1,C.angle,C.efficiency,");
                    detailPrefixBuilder.Append("D.shock,D.voltage,D.type electrictType,D.electricPower,D.lightingPower,D.allPower,D.controlType,");
                    detailPrefixBuilder.Append("E.class adpaterClass,E.version adpaterVersion,E.color adpaterColor,E.cri,E.output,E.pins,");
                    detailPrefixBuilder.Append("F.parts,F.setup,F.size projectSize,F.report,F.baikup,");
                    detailPrefixBuilder.Append("G.in, G.market,G.out,G.delievery, H.facade facade,H.live live,H.size pictureSize,H.grading1 grading1 ");

                    StringBuilder middleBuilder = new StringBuilder();
                    middleBuilder.Append("FROM lighting A,physical B,optics C, electric D, adapterlighting E, project F, business G, picture H ");
                    middleBuilder.Append("WHERE A.physicalID = B.id ");
                    middleBuilder.Append("AND A.opticsID = C.id ");
                    middleBuilder.Append("AND A.electricid = D.id ");
                    middleBuilder.Append("AND A.adapterID = E.id ");
                    middleBuilder.Append("AND A.projectID = F.id ");
                    middleBuilder.Append("AND A.businessID = G.id ");
                    middleBuilder.Append("AND A.pictureID = H.id ");

                    List<MySqlParameter> parameters = new List<MySqlParameter>();
                    if (lightingQuery.HasSeries)
                    {
                        middleBuilder.Append("AND A.series= @series ");
                        parameters.Add(new MySqlParameter()
                        {
                            ParameterName = "@series",
                            DbType = DbType.String,
                            Value = lightingQuery.Series
                        });
                    }

                    if (lightingQuery.HasUsePlace)
                    {
                        middleBuilder.Append("AND A.place= @place ");
                        parameters.Add(new MySqlParameter()
                        {
                            ParameterName = "@place",
                            DbType = DbType.UInt32,
                            Value = lightingQuery.UsePlace
                        });
                    }

                    if (lightingQuery.HasProductClass)
                    {
                        middleBuilder.Append("AND A.class= @class ");
                        parameters.Add(new MySqlParameter()
                        {
                            ParameterName = "@class",
                            DbType = DbType.UInt32,
                            Value = lightingQuery.ProductClass
                        });
                    }

                    if (lightingQuery.HasProductVersion)
                    {
                        middleBuilder.Append("AND A.version= @version ");
                        parameters.Add(new MySqlParameter()
                        {
                            ParameterName = "@version",
                            DbType = DbType.String,
                            Value = lightingQuery.ProductVersion
                        });
                    }

                    if (lightingQuery.HasProductLable)
                    {
                        middleBuilder.Append("AND A.lable= @lable ");
                        parameters.Add(new MySqlParameter()
                        {
                            ParameterName = "@lable",
                            DbType = DbType.String,
                            Value = lightingQuery.ProductLable
                        });
                    }

                    if (lightingQuery.HasStartLightingPower && lightingQuery.HasEndLightingPower)
                    {
                        middleBuilder.Append("AND D.lightingPower BETWEEN @startLightingPower AND @endLightingPower ");
                        parameters.Add(new MySqlParameter()
                        {
                            ParameterName = "@startLightingPower",
                            DbType = DbType.UInt32,
                            Value = UInt32.Parse(lightingQuery.StartLightingPower)
                        });
                        parameters.Add(new MySqlParameter()
                        {
                            ParameterName = "@endLightingPower",
                            DbType = DbType.UInt32,
                            Value = UInt32.Parse(lightingQuery.EndLightingPower)
                        });
                    }
                    else if (lightingQuery.HasStartLightingPower)
                    {
                        middleBuilder.Append("AND D.lightingPower >= @startLightingPower ");
                        parameters.Add(new MySqlParameter()
                        {
                            ParameterName = "@startLightingPower",
                            DbType = DbType.UInt32,
                            Value = UInt32.Parse(lightingQuery.StartLightingPower)
                        });
                    }
                    else if (lightingQuery.HasEndLightingPower)
                    {
                        middleBuilder.Append("AND D.lightingPower <= @endLightingPower ");
                        parameters.Add(new MySqlParameter()
                        {
                            ParameterName = "@endLightingPower",
                            DbType = DbType.UInt32,
                            Value = UInt32.Parse(lightingQuery.EndLightingPower)
                        });
                    }

                    //4.	光源色温下拉菜单分为三档暖<3300K
                    //中性白 3300K-5000K
                    //冷>5000K
                    if (lightingQuery.HasStartTemperature)
                    {
                        switch (lightingQuery.StartTemperature)
                        {
                            case "1":
                                middleBuilder.Append("AND E.color < 3300 ");
                                break;
                            case "2":
                                middleBuilder.Append("AND E.color BETWEEN 3300 AND 5000 ");
                                break;
                            case "3":
                                middleBuilder.Append("AND E.color > 5000 ");
                                break;
                        }
                    }

                    if (lightingQuery.HasStartCri)
                    {
                        switch (lightingQuery.StartCri)
                        {
                            case "1":
                                middleBuilder.Append("AND E.cri < 79 ");
                                break;
                            case "2":
                                middleBuilder.Append("AND E.cri BETWEEN 80 AND 89 ");
                                break;
                            case "3":
                                middleBuilder.Append("AND E.cri >= 90 ");
                                break;
                        }
                    }

                    if (lightingQuery.HasStartMarketPrice && lightingQuery.HasEndMarketPrice)
                    {
                        middleBuilder.Append("AND G.market BETWEEN @startMarket AND @endMarket ");
                        parameters.Add(new MySqlParameter()
                        {
                            ParameterName = "@startMarket",
                            DbType = DbType.Double,
                            Value = double.Parse(lightingQuery.StartMarketPrice)
                        });
                        parameters.Add(new MySqlParameter()
                        {
                            ParameterName = "@endMarket",
                            DbType = DbType.Double,
                            Value = double.Parse(lightingQuery.EndMarketPrice)
                        });
                    }
                    else if (lightingQuery.HasStartMarketPrice)
                    {
                        middleBuilder.Append("AND G.market >= @startMarket ");
                        parameters.Add(new MySqlParameter()
                        {
                            ParameterName = "@startMarket",
                            DbType = DbType.Double,
                            Value = double.Parse(lightingQuery.StartMarketPrice)
                        });
                    }
                    else if (lightingQuery.HasEndMarketPrice)
                    {
                        middleBuilder.Append("AND G.market <= @endMarket ");
                        parameters.Add(new MySqlParameter()
                        {
                            ParameterName = "@endMarket",
                            DbType = DbType.Double,
                            Value = double.Parse(lightingQuery.EndMarketPrice)
                        });
                    }

                    conn.Open();

                    using (DbCommand command = conn.CreateCommand())
                    {
                        StringBuilder sb = new StringBuilder();
                        sb.Append(detailPrefixBuilder.ToString());
                        sb.Append(middleBuilder.ToString() + ";");
                        command.CommandText = sb.ToString();
                        command.Parameters.AddRange(parameters.ToArray());
                        using (var reader = command.ExecuteReader())
                        {
                            if(lightingQuery.HasStartAngle)
                            {
                                while (reader.Read())
                                {
                                    string stringValue;
                                    TryGetValue(reader["angle"], out stringValue);
                                    if (Filter(stringValue, lightingQuery.StartAngle))
                                        resultBuilder.LightingList.Add(CreateLighting5(reader));
                                }
                            }
                            else
                            {
                                while (reader.Read())
                                {
                                    resultBuilder.LightingList.Add(CreateLighting5(reader));
                                }
                            }

                        }
                    }

                }
            }
            catch (Exception ex)
            {

            }
            return resultBuilder.Build();
        }


        //SELECT DISTINCT A.class productClass, A.lable lable, A.series series, E.class adpaterClass, A.place Place,  F.setup setup, H.facade facade
        //FROM lighting A,adapterlighting E, project F, picture H
        //WHERE A.projectID = F.id
        //AND A.adapterID = E.id
        //AND A.pictureID = H.id
        public Lightings QueryRelatedProduct(RelatedProduct parameter)
        {
            Lightings.Builder resultBuilder = new Lightings.Builder();
            try
            {
                using (DbConnection conn = LightingServerContext.CreateConnection())
                {
                    using (DbCommand command = conn.CreateCommand())
                    {
                        conn.Open();

                        StringBuilder sb = new StringBuilder();
                        sb.Append("SELECT DISTINCT A.class productClass, A.lable lable, A.series series, E.class adpaterClass, A.place Place,  F.setup setup, H.facade facade ");
                        sb.Append("FROM lighting A,adapterlighting E, project F, picture H ");
                        sb.Append("WHERE A.projectID = F.id AND A.adapterID = E.id AND A.pictureID = H.id ");
                        if (parameter.HasSeries)
                        {
                            sb.Append("AND A.series = @series ");
                            command.Parameters.Add(new MySqlParameter()
                            {
                                ParameterName = "@series",
                                DbType = DbType.String,
                                Value = parameter.Series
                            });
                        }

                        if (parameter.HasProductClass)
                        {
                            sb.Append("AND A.class = @class ");
                            command.Parameters.Add(new MySqlParameter()
                            {
                                ParameterName = "@class",
                                DbType = DbType.UInt32,
                                Value = parameter.ProductClass
                            });
                        }

                        if (parameter.HasUsePlace)
                        {
                            sb.Append("AND A.place = @place ");
                            command.Parameters.Add(new MySqlParameter()
                            {
                                ParameterName = "@place",
                                DbType = DbType.UInt32,
                                Value = parameter.UsePlace
                            });
                        }

                        if (parameter.HasSetup)
                        {
                            sb.Append("AND F.setup = @setup ");
                            command.Parameters.Add(new MySqlParameter()
                            {
                                ParameterName = "@setup",
                                DbType = DbType.UInt32,
                                Value = parameter.Setup
                            });
                        }

                        if (parameter.HasAdpaterClass)
                        {
                            sb.Append("AND E.class = @adpaterClass ");
                            command.Parameters.Add(new MySqlParameter()
                            {
                                ParameterName = "@adpaterClass",
                                DbType = DbType.UInt32,
                                Value = parameter.AdpaterClass
                            });
                        }

                        sb.Append(";");

                        command.CommandText = sb.ToString();

                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Lighting.Builder itemBuilder = new Lighting.Builder();

                                object productClass = reader["productClass"];
                                if (!DBNull.Value.Equals(productClass))
                                {
                                    uint value;
                                    if (uint.TryParse(productClass.ToString(), out value))
                                        itemBuilder.SetClass(value);
                                }

                                object lable = reader["lable"];
                                if (!DBNull.Value.Equals(lable))
                                {
                                    string value = lable.ToString();
                                    if (!string.IsNullOrEmpty(value))
                                        itemBuilder.SetLable(value);
                                }

                                object series = reader["series"];
                                if (!DBNull.Value.Equals(series))
                                {
                                    string value = series.ToString();
                                    if (!string.IsNullOrEmpty(value))
                                        itemBuilder.SetSeries(value);
                                }

                                object adpaterClass = reader["adpaterClass"];
                                if (!DBNull.Value.Equals(adpaterClass))
                                {
                                    uint value;
                                    if (uint.TryParse(adpaterClass.ToString(), out value))
                                    {
                                        AdapterLighting.Builder adapterBuilder = new AdapterLighting.Builder();
                                        adapterBuilder.SetClass(value);
                                        itemBuilder.SetAdapterLighting(adapterBuilder.Build());
                                    }
                                }

                                uint place;
                                if(TryGetValue(reader["Place"], out place))
                                {
                                    itemBuilder.SetPlace(place);
                                }

                                uint setup;
                                if(TryGetValue(reader["setup"], out setup))
                                {
                                    Project.Builder projectBuilder = new Project.Builder();
                                    projectBuilder.SetSetup(setup);
                                    itemBuilder.SetProject(projectBuilder.Build());
                                }

                                object facade = reader["facade"];
                                if (!DBNull.Value.Equals(facade))
                                {
                                    try
                                    {
                                        string value = facade.ToString();
                                        if (!string.IsNullOrEmpty(value))
                                        {
                                            Picture.Builder pictureBuilder = new Picture.Builder();
                                            string imageUrl = Path.Combine(LightingServerContext.ImageUrl, value);
                                            pictureBuilder.SetFacade(imageUrl);
                                            itemBuilder.SetPicture(pictureBuilder.Build());
                                        }
                                    }
                                    catch
                                    {
                                    }
                                }

                                resultBuilder.LightingList.Add(itemBuilder.Build());
                            }

                        }
                    }
                }
            }
            catch (Exception ex)
            {

            }
            return resultBuilder.Build();
        }

        public Res GetStaticResource()
        {
            Res.Builder builder = new Res.Builder();
            string resSql = "SELECT guid, itemID, itemName FROM res";
            using (DbConnection conn = LightingServerContext.CreateConnection())
            {
                using (DbCommand command = conn.CreateCommand())
                {
                    conn.Open();
                    command.CommandText = resSql;
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            ResItem.Builder itemBuilder = new ResItem.Builder();
                            itemBuilder.SetId(uint.Parse(reader["guid"].ToString()));
                            itemBuilder.SetItemID(uint.Parse(reader["itemID"].ToString()));
                            itemBuilder.SetItemName(reader["itemName"].ToString());
                            builder.Res_List.Add(itemBuilder.Build());
                        }
                    }
                }
            }
            return builder.Build();
        }
    }
}
